package com.exonline.backstage.controller.course;


import cn.hutool.core.util.IdUtil;
import com.exonline.commons.base.IBaseService;
import com.exonline.backstage.entity.Courses;
import com.exonline.backstage.entity.Direction;
import com.exonline.backstage.entity.Subject;
import com.exonline.commons.dao.DaoImpl;
import com.exonline.commons.dao.IDao;
import org.apache.commons.dbutils.handlers.*;


import java.sql.SQLException;
import java.util.*;

/**
 * 科目M层
 *
 * @author YUKI
 * @date 2024/11/27
 */
public class CoursesService implements IBaseService<Courses> {


    IDao<Courses> coursesIDao = new DaoImpl<>();

    @Override
    public List<Courses> queryAll(int pageSize, int pageNo) throws SQLException {
        List<Courses> courses = coursesIDao.queryWithPagingAndConditions("SELECT * FROM courses", null, pageNo, pageSize, new BeanListHandler<>(Courses.class));
        courses.stream().forEach(s->{if(s.getCstatus().equals("0")){
            s.setCstatus("未发布");
        }else {
            s.setCstatus("已发布");
        }
        });
        return courses;
    }

    public List<Courses> queryAll(String condition, int pageSize, int pageNo) throws SQLException {
        try {
            List<Courses> courses = coursesIDao.queryWithPagingAndConditions("SELECT  c.*,t.tname FROM courses c join teacher t on t.id = c.tid", condition, pageNo, pageSize, new BeanListHandler<>(Courses.class));
            courses.stream().forEach(s -> {
                if (s.getCstatus().equals("0")) {
                    s.setCstatus("待审核");
                } else if (s.getCstatus().equals("1")) {
                    try {
                        List<Object[]> objects = coursesIDao.queryForObject("SELECT comment from coursesreviewhistory where cid = ?", new ArrayListHandler(), s.getId());
                        if (objects!=null&&objects.size()>0) {
                            s.setComment((String) objects.get(0)[0]);
                        }
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    }
                    s.setCstatus("已通过");
                } else if (s.getCstatus().equals("2")) {
                    try {
                        List<Object[]> objects = coursesIDao.queryForObject("SELECT comment from coursesreviewhistory where cid = ?", new ArrayListHandler(), s.getId());

                        if (objects!=null&&objects.size()>0) {
                            s.setComment((String) objects.get(0)[0]);
                        }
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    }
                    s.setCstatus("已驳回");
                }
            });
            return courses;
        } catch (SQLException e) {
            throw new SQLException(e);
        }
    }

    public List<Courses> queryAllReviewHistory(String condition, int pageSize, int pageNo) throws SQLException {
        try {

            List<Courses> courses = coursesIDao.queryWithPagingAndConditions("SELECT distinct c.*,t.tname,co.aid,co.aname FROM courses c join teacher t on t.id = c.tid left join coursesreviewhistory co on co.cid = c.id", condition, pageNo, pageSize, new BeanListHandler<>(Courses.class));
            courses.stream().forEach(s -> {
                if (s.getCstatus()!=null&&s.getCstatus().equals("0")) {
                    s.setCstatus("待审核");
                } else if (s.getCstatus().equals("1")) {
                    try {
                        List<Object[]> objects = coursesIDao.queryForObject("SELECT comment from coursesreviewhistory where cid = ?", new ArrayListHandler(), s.getId());
                        if (objects!=null&&objects.size()>0) {
                            s.setComment((String) objects.get(0)[0]);
                        }
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    }
                    s.setCstatus("已通过");
                } else if (s.getCstatus().equals("2")) {
                    try {
                        List<Object[]> objects = coursesIDao.queryForObject("SELECT comment from coursesreviewhistory where cid = ?", new ArrayListHandler(), s.getId());

                        if (objects!=null&&objects.size()>0) {
                            s.setComment((String) objects.get(0)[0]);
                        }
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    }
                    s.setCstatus("已驳回");
                }
            });
            return courses;
        } catch (SQLException e) {
            throw new SQLException(e);
        }
    }



    @Override
    public Courses queryOne(String id) throws SQLException {
        Courses courses = coursesIDao.queryForObject("SELECT\n" +
                "\tc.*,t.tname as tname, \n" +
                "\t(select count(*) from coursesresource where cid = c.id) AS coursescount\n" +
                "FROM\n" +
                "\tcourses c\n" +
                "\tJOIN teacher t ON t.id = c.tid \n" +
                "WHERE\n" +
                "\tc.id = ?", new BeanHandler<Courses>(Courses.class), id);
        List<String> urls = coursesIDao.queryForObject("SELECT * FROM coursesresource WHERE cid = ?", new ColumnListHandler<>("videourl"), id);
        Map<String,Object> map = new HashMap<>();
        for (String url : urls) {
            String title = url.substring(url.lastIndexOf("-") - 2, url.lastIndexOf("."));
            if(!map.containsKey(title)){
                map.put(title,url);
            }
        }
        System.out.println(map);

        System.out.println(urls);
        courses.setVideourls(map);
        return courses;
    }

    @Override
    public void add(Object... obj) throws SQLException {
        coursesIDao.updateWithTransaction("INSERT INTO courses VALUES(?,?,?,?,?,?,?,?,?,?)", obj);
    }

    @Override
    public void update(Object... obj) throws SQLException {
        String status = String.valueOf(obj[0]);
        String cid =  String.valueOf(obj[1]);
        String comment = (String) obj[2];
        String aid = (String) obj[3];
        coursesIDao.updateWithTransaction("UPDATE courses SET cstatus = ? WHERE id = ?", status,cid);
        String hid = IdUtil.simpleUUID();
        coursesIDao.updateWithTransaction("INSERT INTO coursesreviewhistory(id,cid,aid,status,comment) values(?,?,?,?,?)",hid,cid,aid,status,comment);
    }

    @Override
    public void deleteById(String id) throws SQLException {
        coursesIDao.updateWithTransaction("DELETE FROM courses WHERE id=?", id);
    }

    public void deleteByIdReview(String cid) throws SQLException {
        coursesIDao.updateWithTransaction("DELETE FROM coursesreviewhistory WHERE cid=?", cid);
    }

    public List<Direction> structurequery(String condition, int pageSize, int pageNo) throws SQLException {
        //获取查询条件
        String sqlCondition = null;
        if ("free".equals(condition)) {
            sqlCondition = "cprice like " + "'0%'";
        } else if ("paid".equals(condition)) {
            sqlCondition = "cprice not like" + "'0%'";
        } else if ("tfree".equals(condition)) {
            sqlCondition = "cprice like " + "'0%'" + " AND cstatus = " + "'1'";
        } else if ("tpaid".equals(condition)) {
            sqlCondition = "cprice not like" + "'0%'" + " AND cstatus = " + "'1'";
        } else if ("ffree".equals(condition)) {
            sqlCondition = "cprice  like" + "'0%'" + " AND cstatus = " + "'0'";
        } else if ("fpaid".equals(condition)) {
            sqlCondition = "cprice  not like" + "'0%'" + " AND cstatus =" + "'0'";
        }
        List<Direction> directions = coursesIDao.queryWithPagingAndConditions("SELECT DISTINCT cdirection AS directionName FROM courses", null, 1, 100, new BeanListHandler<>(Direction.class));
        List<Subject> subjects = coursesIDao.queryWithPagingAndConditions("SELECT DISTINCT csubject AS subjectName FROM courses", null, 1, 100, new BeanListHandler<>(Subject.class));
        List<Courses> courses = coursesIDao.queryWithPagingAndConditions("SELECT c.*,t.tname FROM courses c join teacher t on t.id = c.tid", sqlCondition, pageNo, pageSize, new BeanListHandler<>(Courses.class));
        System.out.println(courses);
        //以方向为单位,返回课程信息
        for (Direction direction : directions) {
            String directionName = direction.getDirectionName();


            for (Courses cours : courses) {
                String courseSubjectName = cours.getCsubject();
                String courseDirectionName = cours.getCdirection();

                for (Subject subject : subjects) {

                    if (courseSubjectName.equals(subject.getSubjectName()) && !subject.getCourses().contains(cours)) {
                        subject.addCourses(cours);
                    }
                }

                if (courseDirectionName.equals(directionName)) {
                    for (Subject subject : subjects) {
                        if (courseSubjectName.equals(subject.getSubjectName()) && !direction.getSubjects().contains(subject)) {
                            direction.addSubjects(subject);
                        }
                    }
                }
            }
        }
        return directions;
    }

    public void queryAllReview() {

    }
}
